<CFQUERY NAME="qryCINLEAVEAPPSM" DATASOURCE="#client.company_dsn#">
	SELECT *
	FROM CINLEAVEAPPSM
	WHERE PERSONNELIDNO = '#GETENTRY.PERSONNELIDNO#' AND DOCNUMBER = '#GETENTRY.DOCNUMBER#'
</CFQUERY>
<!--- Modified by Maan 05292007 -> Validate rest day/day off and holidays --->
<!--- check subsked --->
<CFQUERY NAME="qryCINWSSUBSKED" DATASOURCE="#client.company_dsn#">
	SELECT A.REFERENCEDATE, A.PERSONNELIDNO, A.PATTERNCODE
	FROM CINWSSUBSKED A, CLKWSPATTERN B
	WHERE A.PATTERNCODE = B.PATTERNCODE AND A.PERSONNELIDNO = '#GETENTRY.PERSONNELIDNO#' AND  B.DAYTYPE IN ('DO','RD')
</CFQUERY>
<!--- check tempsked --->
<CFQUERY NAME="qryCINWSTEMPSKED" DATASOURCE="#client.company_dsn#">
	SELECT A.PERSONNELIDNO, A.WORKSKEDCODE, A.STARTDATE, A.ENDDATE, B.MONDAY, B.TUESDAY, B.WEDNESDAY, B.THURSDAY, B.FRIDAY, B.SATURDAY, B.SUNDAY
	FROM CINWSTEMPSKED A, CLKWORKSKED B
	WHERE (PERSONNELIDNO = '#GETENTRY.PERSONNELIDNO#') AND (A.ENDDATE =
		(SELECT MAX(ENDDATE) AS ENDDATE
		FROM CINWSTEMPSKED
		WHERE PERSONNELIDNO = '#GETENTRY.PERSONNELIDNO#')) AND A.WORKSKEDCODE = B.WORKSKEDCODE
</CFQUERY>
<!--- check permanent sked --->
<CFQUERY NAME="qryCINWSMASTER" DATASOURCE=#client.company_dsn#>
	SELECT A.PERSONNELIDNO, A.WORKSKEDCODE, A.STARTDATE, B.MONDAY, B.TUESDAY, B.WEDNESDAY, B.THURSDAY, B.FRIDAY, B.SATURDAY, B.SUNDAY
	FROM CINWSMASTER A, CLKWORKSKED B
	WHERE (A.PERSONNELIDNO = '#GETENTRY.PERSONNELIDNO#') 
	AND (A.ACTIVESKED = 'Y') AND (A.STARTDATE =
		(SELECT MAX(STARTDATE) AS STARTDATE
		FROM CINWSMASTER
		WHERE (PERSONNELIDNO = '#GETENTRY.PERSONNELIDNO#') AND (ACTIVESKED = 'Y')))
		AND A.WORKSKEDCODE = B.WORKSKEDCODE
</CFQUERY>
<!--- check holiday --->
<CFQUERY NAME="qryCLKWSHOLIDAYS" DATASOURCE=#client.company_dsn#>
	SELECT HOLIDAYMONTH,HOLIDAYDAY,HALF_DAY
	FROM CLKWSHOLIDAYS
</CFQUERY>			
<CFSET dSubSked = "">
<CFIF qryCINWSSUBSKED.RECORDCOUNT NEQ 0>
	<CFLOOP QUERY="qryCINWSSUBSKED">
		<CFSET dSubSked = ListAppend(dSubSked,DateFormat(qryCINWSSUBSKED.REFERENCEDATE,"mm/dd/yyyy"))>
	</CFLOOP>
</CFIF>
<CFSET theDate= #DATEFORMAT(GETENTRY.STARTINGDATE, "mm/dd/yyyy")#>
<CFSET sNoofDays= DateDiff("d", "#GETENTRY.STARTINGDATE#", "#GETENTRY.ENDINGDATE#") + 1>
<cfset dates = "">
<!--- start the validation, process date by date --->
<CFIF qryCINLEAVEAPPSM.STAFFPID NEQ "">
	<CFSET pID = qryCINLEAVEAPPSM.STAFFPID>
<CFELSE>
	<CFSET pID = qryCINLEAVEAPPSM.PERSONNELIDNO>
</CFIF>
<CFSET isHoliday = "No">
<CFLOOP index="holidaycount" FROM="1" TO="#sNoofDays#" STEP="1">
	<CFSET nDay = DayOfWeek(theDate)>
	<CFSET nNonHolidayMonth=DateFormat("#theDate#","m")>
	<CFSET nNonHolidayDay=DateFormat("#theDate#","d")>
	<CFSET isHoliday = "No">
	<CFSET stheDay = "">
	
	<!--- subsked first--->
	<CFIF ListFind(dSubSked,DateFormat(theDate,"mm/dd/yyyy")) NEQ "0"><!--- there is a nonworking subsked --->
	<cfset session.11wats2 = "subsked">
	<!--- do nothing --->
	<CFELSEIF qryCINWSTEMPSKED.ENDDATE GTE theDate AND theDate NEQ qryCINWSSUBSKED.REFERENCEDATE>
	<!--- if not subsked, tempsked next --->
	<cfset session.11watt2 = "tempsked">
		<CFIF qryCINWSTEMPSKED.STARTDATE LTE getEntry.STARTINGDATE>
			<CFIF qryCINWSTEMPSKED.MONDAY    EQ "WD"><CFSET stheDay = ListAppend(stheDay,"2")></CFIF>
			<CFIF qryCINWSTEMPSKED.TUESDAY   EQ "WD"><CFSET stheDay = ListAppend(stheDay,"3")></CFIF>
			<CFIF qryCINWSTEMPSKED.WEDNESDAY EQ "WD"><CFSET stheDay = ListAppend(stheDay,"4")></CFIF>
			<CFIF qryCINWSTEMPSKED.THURSDAY  EQ "WD"><CFSET stheDay = ListAppend(stheDay,"5")></CFIF>
			<CFIF qryCINWSTEMPSKED.FRIDAY    EQ "WD"><CFSET stheDay = ListAppend(stheDay,"6")></CFIF>
			<CFIF qryCINWSTEMPSKED.SATURDAY  EQ "WD"><CFSET stheDay = ListAppend(stheDay,"7")></CFIF>
			<CFIF qryCINWSTEMPSKED.SUNDAY    EQ "WD"><CFSET stheDay = ListAppend(stheDay,"1")></CFIF>
		</CFIF>
		<!--- deduct holidays that are rest day / day off--->
		<CFIF listFindNoCase(stheDay,nDay) NEQ "0">
			<CFLOOP QUERY="qryCLKWSHOLIDAYS">
				<CFIF HOLIDAYMONTH EQ nNonHolidayMonth AND HOLIDAYDAY EQ nNonHolidayDay>
					<CFSET isHoliday = "Yes">
					<CFBREAK>
				</CFIF>
			</CFLOOP>
			<CFIF isHoliday EQ "No">
				<CFOUTPUT QUERY="qryCINLEAVEAPPSM">
					<CFIF LEAVEHOURS EQ "" OR LEAVEHOURS EQ "0">
						<CFSET nLVhrs = "8">
					<CFELSE>
						<CFSET nLVhrs = LEAVEHOURS>
					</CFIF>
					<CFQUERY NAME="insertCINLEAVEAPPSD" DATASOURCE="#client.company_dsn#">
						INSERT INTO CINLEAVEAPPSD(PERSONNELIDNO,REFERENCEDATE,LEAVEHOURS,LEAVETYPE,RECCREATEDBY,RECDATECREATED,USERID,DATELASTUPDATE,TIMELASTUPDATE,NOOFDAYS,DOCNUMBER,APPROVED,WITHPAY)
						VALUES('#pID#','#theDate#','#nLVhrs#','#LEAVETYPE#','#RECCREATEDBY#','#RECDATECREATED#','#USERID#','#DATELASTUPDATE#','#TIMELASTUPDATE#','1','#DOCNUMBER#','Y','#WITHPAY#')
					</CFQUERY>
				</CFOUTPUT>
			</CFIF>
		</CFIF>
	<CFELSE>
	<!--- permanent sked --->
	<cfset session.11watp2 = "permanent">
		<CFIF qryCINWSMASTER.STARTDATE LTE getEntry.STARTINGDATE>
			<CFIF qryCINWSMASTER.MONDAY    EQ "WD"><CFSET stheDay = ListAppend(stheDay,"2")></CFIF>
			<CFIF qryCINWSMASTER.TUESDAY   EQ "WD"><CFSET stheDay = ListAppend(stheDay,"3")></CFIF>
			<CFIF qryCINWSMASTER.WEDNESDAY EQ "WD"><CFSET stheDay = ListAppend(stheDay,"4")></CFIF>
			<CFIF qryCINWSMASTER.THURSDAY  EQ "WD"><CFSET stheDay = ListAppend(stheDay,"5")></CFIF>
			<CFIF qryCINWSMASTER.FRIDAY    EQ "WD"><CFSET stheDay = ListAppend(stheDay,"6")></CFIF>
			<CFIF qryCINWSMASTER.SATURDAY  EQ "WD"><CFSET stheDay = ListAppend(stheDay,"7")></CFIF>
			<CFIF qryCINWSMASTER.SUNDAY    EQ "WD"><CFSET stheDay = ListAppend(stheDay,"1")></CFIF>
		</CFIF>
		<!--- deduct holidays that are not rest day / day off--->
		<!--- stheDay is the RD, DO; nDay is the currently processed day --->
		<CFIF listFindNoCase(stheDay,nDay) NEQ "0">
			<CFLOOP QUERY="qryCLKWSHOLIDAYS">
				<CFIF HOLIDAYMONTH EQ nNonHolidayMonth AND HOLIDAYDAY EQ nNonHolidayDay>
					<CFSET isHoliday = "Yes">
					<CFBREAK>
				</CFIF>
			</CFLOOP>
			<CFIF isHoliday EQ "No">
				<CFOUTPUT QUERY="qryCINLEAVEAPPSM">
					<CFIF LEAVEHOURS EQ "" OR LEAVEHOURS EQ "0">
						<CFSET nLVhrs = "8">
					<CFELSE>
						<CFSET nLVhrs = LEAVEHOURS>
					</CFIF>
					<CFQUERY NAME="insertCINLEAVEAPPSD" DATASOURCE="#client.company_dsn#">
						INSERT INTO CINLEAVEAPPSD(PERSONNELIDNO,REFERENCEDATE,LEAVEHOURS,LEAVETYPE,RECCREATEDBY,RECDATECREATED,USERID,DATELASTUPDATE,TIMELASTUPDATE,NOOFDAYS,DOCNUMBER,APPROVED,WITHPAY)
						VALUES('#pID#','#theDate#','#nLVhrs#','#LEAVETYPE#','#RECCREATEDBY#','#RECDATECREATED#','#USERID#','#DATELASTUPDATE#','#TIMELASTUPDATE#','1','#DOCNUMBER#','Y','#WITHPAY#')
					</CFQUERY>
				</CFOUTPUT>
			</CFIF>
		</CFIF>
	</CFIF>
	<CFSET theDate= DATEFORMAT(DateAdd("D",1, "#theDate#"))>
</CFLOOP>
<cfset session.111mydate = dates>
<!--- this is the original code 
<CFSET theDate= DATEFORMAT("#qryCINLEAVEAPPSM.STARTINGDATE#")>
<CFSET sNoofDays= DateDiff("d", "#qryCINLEAVEAPPSM.STARTINGDATE#", "#qryCINLEAVEAPPSM.ENDINGDATE#") + 1>
<CFOUTPUT QUERY="qryCINLEAVEAPPSM">
	<CFLOOP index="noofdays" FROM="1" TO="#sNoofDays#" STEP="1">
		<CFSET ValidateDate=DayOfWeekAsString(DayOfWeek("#theDate#"))>
		--- validate rest day/day off and holidays 
		<CFIF ValidateDate NEQ "Sunday">
			<CFIF ValidateDate NEQ "Saturday">---
				<CFIF qryCINLEAVEAPPSM.STAFFPID NEQ "">
					<CFQUERY NAME="insertCINLEAVEAPPSD" DATASOURCE="#client.company_dsn#">
						INSERT INTO CINLEAVEAPPSD(PERSONNELIDNO,REFERENCEDATE,LEAVEHOURS,LEAVETYPE,RECCREATEDBY,RECDATECREATED,USERID,DATELASTUPDATE,TIMELASTUPDATE,NOOFDAYS,DOCNUMBER,APPROVED,WITHPAY)
						VALUES('#STAFFPID#','#theDate#','#LEAVEHOURS#','#LEAVETYPE#','#RECCREATEDBY#','#RECDATECREATED#','#USERID#','#DATELASTUPDATE#','#TIMELASTUPDATE#','#NOOFDAYS#','#DOCNUMBER#','N','#WITHPAY#')
					</CFQUERY>
				<CFELSE>
					<CFQUERY NAME="insertCINLEAVEAPPSD" DATASOURCE="#client.company_dsn#">
						INSERT INTO CINLEAVEAPPSD(PERSONNELIDNO,REFERENCEDATE,LEAVEHOURS,LEAVETYPE,RECCREATEDBY,RECDATECREATED,USERID,DATELASTUPDATE,TIMELASTUPDATE,NOOFDAYS,DOCNUMBER,APPROVED,WITHPAY)
						VALUES('#PERSONNELIDNO#','#theDate#','#LEAVEHOURS#','#LEAVETYPE#','#RECCREATEDBY#','#RECDATECREATED#','#USERID#','#DATELASTUPDATE#','#TIMELASTUPDATE#','#NOOFDAYS#','#DOCNUMBER#','N','#WITHPAY#')
					</CFQUERY>
				</CFIF>
		---	</CFIF>
		</CFIF>---
		<CFSET ValidateDate="">
		<CFSET theDate= DATEFORMAT(DateAdd("D",1, "#theDate1#"))>
	</CFLOOP>
</CFOUTPUT>
--->
